 use musicman
go

if exists (select * from musicman.information_schema.routines where 
routine_name = 'DeleteUser' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[DeleteUser]
end 
go

/****** Object:  StoredProcedure [dbo].[DeleteUser]    Script Date: 12/03/2008 16:05:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteUser]
	@PersonID bigint
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
		
	DELETE FROM  MUSICMAN.DBO.USERDOWNLOADS WHERE UserID = @PersonID
	DELETE FROM  MUSICMAN.DBO.USERROLES WHERE UserID = @PersonID
	DELETE FROM  MUSICMAN.DBO.USERFEEDBACK WHERE Personid = @PersonID
	DELETE FROM  MUSICMAN.DBO.USERSTATS WHERE Personid = @PersonID
	DELETE FROM  MUSICMAN.DBO.USERS WHERE Personid = @PersonID

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();
	RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
END CATCH

END
go

-- UPDATE USER STATS

if exists (select * from musicman.information_schema.routines where 
routine_name = 'UpdateUserStats' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[UpdateUserStats]
end 
go

CREATE PROCEDURE [dbo].[UpdateUserStats]
	@PersonId bigint,
	@web int,
	@client int

AS
BEGIN
 IF NOT EXISTS(SELECT * FROM MUSICMAN.DBO.USERSTATS WHERE PersonID = @PersonId)
 BEGIN
	INSERT INTO MUSICMAN.DBO.USERSTATS(PERSONID, NUMLOGSWEB, NUMLOGSCLIENT) VALUES (@PersonId, @web, @client);
 END 
 ELSE 
 BEGIN 
    UPDATE MUSICMAN.DBO.USERSTATS SET NUMLOGSWEB = NUMLOGSWEB + @web, NUMLOGSCLIENT = NUMLOGSCLIENT + @client WHERE PERSONID = @PersonId
 END
END

-- UPDATE DOWNLOAD STATS

if exists (select * from musicman.information_schema.routines where 
routine_name = 'UpdateDownloadStats' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[UpdateDownloadStats]
end 
go

CREATE PROCEDURE [dbo].[UpdateDownloadStats]
	@PersonId bigint,
	@SearchFileID bigint,
	@num int
AS
BEGIN
 IF NOT EXISTS(SELECT * FROM MUSICMAN.DBO.FILESSTATS WHERE SEARCHFILEID = @SearchFileID)
 BEGIN
	INSERT INTO MUSICMAN.DBO.FILESSTATS(SEARCHFILEID, TOTALDOWNLOADS) VALUES (@SearchFileID, @num);
 END 
 ELSE 
 BEGIN 
    UPDATE MUSICMAN.DBO.FILESSTATS SET TOTALDOWNLOADS = TOTALDOWNLOADS + @num WHERE SEARCHFILEID = @SearchFileID
 END
 
 DECLARE @fileLenght bigint;
 SELECT @fileLenght = length from musicman.dbo.searchfile where searchfileid = @SearchFileID
 UPDATE MUSICMAN.DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1, TOTALLENGTH = TOTALLENGTH + @fileLenght WHERE PERSONID = @PersonId

END


-- [dbo].[ClearJobData]

if exists (select * from musicman.information_schema.routines where 
routine_name = 'ClearJobData' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[ClearJobData]
end 
go


/****** Object:  StoredProcedure [dbo].[ClearJobData]    Script Date: 01/16/2009 12:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ClearJobData]
	@SearchJobID bigint
AS
BEGIN

BEGIN TRANSACTION;
BEGIN TRY

    -- delete all user downloads
	delete from musicman.dbo.userDownloads where SearchFielID in 
	(select filee.SearchFileID from musicman.dbo.SearchJob job
		join musicman.dbo.SearchLocation location on job.SearchJobID = location.SearchJobID
		join musicman.dbo.SearchFolder folder on location.SearchLocationID = folder.SearchLocationID
		join musicman.dbo.SearchFile filee on folder.SearchFolderID = filee.SearchFolderID
		join musicman.dbo.userDownloads ud on ud.SearchFielID = filee.SearchFileID
	 where job.SearchJobID = @SearchJobID)

    -- delete all files stats
	delete from musicman.dbo.filesstats where SearchFileID in 
	(select filee.SearchFileID from musicman.dbo.SearchJob job
		join musicman.dbo.SearchLocation location on job.SearchJobID = location.SearchJobID
		join musicman.dbo.SearchFolder folder on location.SearchLocationID = folder.SearchLocationID
		join musicman.dbo.SearchFile filee on folder.SearchFolderID = filee.SearchFolderID
		join musicman.dbo.filesstats fs on fs.SearchFileID = filee.SearchFileID
	 where job.SearchJobID = @SearchJobID)

    -- delete all files stats
	delete from musicman.dbo.filesstats where SearchFileID in 
	(select filee.SearchFileID from musicman.dbo.SearchJob job
		join musicman.dbo.SearchLocation location on job.SearchJobID = location.SearchJobID
		join musicman.dbo.SearchFolder folder on location.SearchLocationID = folder.SearchLocationID
		join musicman.dbo.SearchFile filee on folder.SearchFolderID = filee.SearchFolderID
		join musicman.dbo.filesstats fs on fs.SearchFileID = filee.SearchFileID
	 where job.SearchJobID = @SearchJobID)

	-- delete all files
	delete from musicman.dbo.SearchFile where SearchFileID in 
	(select filee.SearchFileID from musicman.dbo.SearchJob job
		join musicman.dbo.SearchLocation location on job.SearchJobID = location.SearchJobID
		join musicman.dbo.SearchFolder folder on location.SearchLocationID = folder.SearchLocationID
		join musicman.dbo.SearchFile filee on folder.SearchFolderID = filee.SearchFolderID
	 where job.SearchJobID = @SearchJobID)

	-- delete folders
	delete from musicman.dbo.SearchFolder where SearchFolderID in 
	(select folder.SearchFolderID from musicman.dbo.SearchJob job
		join musicman.dbo.SearchLocation location on job.SearchJobID = location.SearchJobID
		join musicman.dbo.SearchFolder folder on location.SearchLocationID = folder.SearchLocationID
	 where job.SearchJobID = @SearchJobID)

	-- delete locations		
	delete from musicman.dbo.SearchLocation where SearchLocationID in 
	(select location.SearchLocationID from musicman.dbo.SearchJob job
		join musicman.dbo.SearchLocation location on job.SearchJobID = location.SearchJobID
	 where job.SearchJobID = @SearchJobID)

	COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();
	RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
END CATCH

END


use musicman
go
-- UPDATE DOWNLOAD STATS

if exists (select * from musicman.information_schema.routines where 
routine_name = 'UpdateDownloadStats' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[UpdateDownloadStats]
end 
go

CREATE PROCEDURE [dbo].[UpdateDownloadStats]
	@PersonId bigint,
	@SearchFileID bigint,
	@num int
AS
BEGIN
 IF NOT EXISTS(SELECT * FROM MUSICMAN.DBO.FILESSTATS WHERE SEARCHFILEID = @SearchFileID)
 BEGIN
	INSERT INTO MUSICMAN.DBO.FILESSTATS(SEARCHFILEID, TOTALDOWNLOADS) VALUES (@SearchFileID, @num);
 END 
 ELSE 
 BEGIN 
    UPDATE MUSICMAN.DBO.FILESSTATS SET TOTALDOWNLOADS = TOTALDOWNLOADS + @num WHERE SEARCHFILEID = @SearchFileID
 END
 
 DECLARE @fileLenght bigint;
 SELECT @fileLenght = length from musicman.dbo.searchfile where searchfileid = @SearchFileID
 IF (@SearchFileID != 0)
 BEGIN 
	UPDATE MUSICMAN.DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1, TOTALLENGTH = TOTALLENGTH + @fileLenght WHERE PERSONID = @PersonId
 END 
 ELSE 
 BEGIN
	UPDATE MUSICMAN.DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1 WHERE PERSONID = @PersonId
 END
END

use musicman
go
-- UPDATE DOWNLOAD STATS

if exists (select * from musicman.information_schema.routines where 
routine_name = 'UpdateDownloadStats' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[UpdateDownloadStats]
end 
go

CREATE PROCEDURE [dbo].[UpdateDownloadStats]
	@PersonId bigint,
	@SearchFileID bigint,
	@num int
AS
BEGIN
 IF (@SearchFileID != 0)
 BEGIN  
	 IF NOT EXISTS(SELECT * FROM MUSICMAN.DBO.FILESSTATS WHERE SEARCHFILEID = @SearchFileID)
	 BEGIN
		INSERT INTO MUSICMAN.DBO.FILESSTATS(SEARCHFILEID, TOTALDOWNLOADS) VALUES (@SearchFileID, @num);
	 END 
	 ELSE 
	 BEGIN 
		UPDATE MUSICMAN.DBO.FILESSTATS SET TOTALDOWNLOADS = TOTALDOWNLOADS + @num WHERE SEARCHFILEID = @SearchFileID
	 END
 END
 
 DECLARE @fileLenght bigint;
 SELECT @fileLenght = length from musicman.dbo.searchfile where searchfileid = @SearchFileID
 IF (@SearchFileID != 0)
 BEGIN 
	UPDATE MUSICMAN.DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1, TOTALLENGTH = TOTALLENGTH + @fileLenght WHERE PERSONID = @PersonId
 END 
 ELSE 
 BEGIN
	UPDATE MUSICMAN.DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1 WHERE PERSONID = @PersonId
 END
END

